import traceback
from contextlib import contextmanager
from urllib import parse

from sqlalchemy import create_engine, select, Column, Integer, String, BigInteger, VARCHAR, func, DateTime, inspect, or_
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.exc import SQLAlchemyError
from functools import wraps

"""
使用model类创建的,

另外还有一种方法使用的是Table创建
"""


UserBase = declarative_base()

@contextmanager
def get_session():
    DATABASE_HOST = 'localhost'
    DATABASE_PORT = 55432
    DATABASE_NAME = 'init'  # 数据库名称
    DATABASE_USER = 'root'  # 数据库用户名
    DATABASE_PASSWORD = '123456'  # 数据库密码

    DATABASE_URL = "postgresql://{}:{}@{}:{}/{}".format(DATABASE_USER, parse.quote_plus(DATABASE_PASSWORD),
                                                        DATABASE_HOST,
                                                        DATABASE_PORT, DATABASE_NAME)
    engine = create_engine(DATABASE_URL)
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        yield session
        session.commit()
    except SQLAlchemyError as e:
        session.rollback()
        raise
    finally:
        session.close()


class QueryBuilder:
    def __init__(self, model):
        self.model = model
        self.query = select(model)

    def select(self, *columns):
        if not columns:
            columns = self.get_column_names(self.model)
        self.query = self.query.with_only_columns(*columns)
        return self

    def filter(self, *conditions):
        self.query = self.query.filter(*conditions)
        return self

    def execute_all(self):
        with get_session() as session:
            result = session.execute(self.query)
            return list(map(lambda x: dict(x), result.all()))

    def execute_first(self):
        with get_session() as session:
            result = session.execute(self.query)
            return dict(result.first())

    def execute_scalar(self):
        with get_session() as session:
            result = session.execute(self.query)
            return result.scalar()

    def get_column_names(self, model_class):
        inspector = inspect(model_class)
        column_names = [eval(f"{model_class.__name__}.{column.name}") for column in inspector.columns]
        return column_names

class BaseModel:
    def save(self):
        with get_session() as session:
            session.add(self)

    def delete(self):
        with get_session() as session:
            session.delete(self)


def handle_exceptions(func):
    @wraps(func)
    def wrapper(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except SQLAlchemyError as e:
            # 处理数据库异常
            print(f"数据库操作出错：{traceback.format_exc(15)}")
            # 可以选择回滚事务或进行其他处理
    return wrapper


# 创建一个模型类
class UserModel(BaseModel, UserBase):
    __tablename__ = 'user_account'

    id = Column(BigInteger, primary_key=True)
    name = Column(VARCHAR(20), unique=True)
    password = Column(VARCHAR(200))
    email_address = Column(VARCHAR(50), unique=True)
    phone = Column(VARCHAR(20), unique=True)
    real_auth = Column(Integer, nullable=False, server_default='0')
    create_time = Column(DateTime(timezone=True), server_default=func.current_timestamp())

    def __init__(self, password=None, email_address=None, name=None, phone=None):
        self.name = name
        self.password = password
        self.email_address = email_address
        self.phone = phone

    def __repr__(self):
        return f"{self.id}:{self.phone}"


# 使用封装的查询构造器和异常处理装饰器
@handle_exceptions
def get_users():
    # u = UserModel(name="djy", password="123456", phone="17815502416")
    # u.save()
    query = QueryBuilder(UserModel).select(func.count(UserModel.id)).filter(or_(UserModel.real_auth < 2))
    result = query.execute_scalar()
    return result
# .scalar()
# 调用查询方法
users = get_users()
print(users)
